import uuid
from package.connector import sql_db, elastic_db, prometheus_db


class BuildSQL:
    """数据库 点选搜索拼接"""

    @staticmethod
    def build_select(select_fields: list) -> str:
        select_parts = []
        for field in select_fields:
            func = field.get('func', '')
            field_name = field['field']
            alias = field.get('alias', '')

            part = field_name if func else f"{func}({field_name})"
            part += f" AS {alias}" if alias else ''
            select_parts.append(part)
        return ", ".join(select_parts) if select_parts else "*"

    @staticmethod
    def build_filters(conditions: list, params: dict) -> str:
        clauses = []
        for cond in conditions:
            if 'logic' in cond:  # 处理逻辑组 (AND/OR)
                sub_clause = BuildSQL.build_filters(cond['conditions'], params)
                if sub_clause:
                    clauses.append(f"({sub_clause})")
            else:
                field = cond['field']
                operator = cond['operator'].upper()
                value = cond['value']

                if operator == 'IN':
                    placeholder = uuid.uuid4().hex
                    params[placeholder] = tuple(value)
                    clauses.append(f"{field} {operator} :{placeholder}")
                elif operator == 'BETWEEN':
                    placeholder_1 = uuid.uuid4().hex
                    placeholder_2 = uuid.uuid4().hex
                    params.update(dict(zip([placeholder_1, placeholder_2], tuple(value))))
                    clauses.append(f"{field} BETWEEN :{placeholder_1} AND :{placeholder_1}")
                else:
                    placeholder = uuid.uuid4().hex
                    params[placeholder] = value
                    clauses.append(f"{field} {operator} :{placeholder}")

        return " AND ".join(clauses) if clauses else ""

    @staticmethod
    def build_group_by(group_fields: list) -> str:
        return ", ".join(group_fields) if group_fields else ""

    @staticmethod
    def build_order_by(order_fields: list) -> str:
        parts = []
        for item in order_fields:
            field = item['field']
            sort = item.get('sort', 'ASC').upper()
            if field and sort in {'ASC', 'DESC'}:
                parts.append(f"{field} {sort}")
        return ", ".join(parts) if parts else ""

    @staticmethod
    def build_sql(query_params: dict) -> dict:
        params = dict()
        select_clause = BuildSQL.build_select(query_params.get('select', []))
        table = query_params['table']
        from_clause = f"FROM {table}"

        # WHERE
        where_clause = BuildSQL.build_filters(query_params.get('filters', []), params)
        where_clause = f"WHERE {where_clause}" if where_clause else ""

        # GROUP BY
        group_clause = BuildSQL.build_group_by(query_params.get('group_by', []))
        group_clause = f"GROUP BY {group_clause}" if group_clause else ""

        # ORDER BY
        order_clause = BuildSQL.build_order_by(query_params.get('order_by', []))
        order_clause = f"ORDER BY {order_clause}" if order_clause else ""

        # LIMIT
        limit = query_params.get('limit')
        limit_clause = f"LIMIT {limit}" if isinstance(limit, int) and limit > 0 else ""

        # 组合SQL
        sql_ = " ".join(filter(None, [
            f"SELECT {select_clause}",
            from_clause,
            where_clause,
            group_clause,
            order_clause,
            limit_clause
        ])) + ";"
        return {'sql': sql_, 'params': params}


builder = BuildSQL()


class Search:

    SearchClass = None

    def __init__(self, configure=None, condition=None):
        self.configure = None
        self.condition = None
        self.connector = None

        if configure and condition:
            self.init(configure, condition)

    def init(self, configure, condition):
        self.configure = configure
        self.condition = condition
        self.connector = self.SearchClass(**self.configure)

    def build_sql_params(self):
        if self.condition.get('sql'):
            return
        self.condition = builder.build_sql(self.condition)

    def search(self):
        return self.connector.search(**self.condition)


class Elasticsearch(Search):

    SearchClass = elastic_db.ElasticsearchDB


class Oracle(Search):
    SearchClass = sql_db.Oracle

    def search(self):
        self.build_sql_params()
        return self.connector.execute_sql(**self.condition)


class MySQL(Search):
    SearchClass = sql_db.MySQL

    def search(self):
        self.build_sql_params()
        return self.connector.execute_sql(**self.condition)

    def help(self):
        """搜索推荐 库/表/字段"""
        pass


class Postgres(Search):
    SearchClass = sql_db.Postgres

    def search(self):
        self.build_sql_params()
        return self.connector.execute_sql(**self.condition)


class Prometheus(Search):

    SearchClass = prometheus_db.Prometheus

    def search(self, **search_configure):
        return self.connector.query(**search_configure)


proxy_map = {item.__name__: item for item in Search.__subclasses__()}


if __name__ == '__main__':
    query_params = {
        "table": "configure",
        "select": [
            {"field": "name", "alias": "部门"}
        ],
        "filters": [
            {"field": "name", "operator": "!=", "value": "role_map"},
            {"field": "name", "operator": "IN", "value": ['black_ip', 'disk_threshold']}
        ],
        "order_by": [{"field": "部门", "sort": "DESC"}],
        "limit": 10
    }
    p = Postgres(configure=dict(url='postgresql+psycopg2://system2:system2@192.168.101.79:5432/system2'),
                 condition=query_params
                 )
    resp = p.search()
    print(resp)
